import re
from openpyxl import workbook, load_workbook
import  shelve
from config import database_path

class find_sheet_table():
    def __init__(self, sheet_values, table_name, header=1, null_row=0,) -> None:
        self.sheet_values = sheet_values
        self.table_name = table_name
        self.null_row = null_row
        self.header = header
        self.new_table = self.__get_start_row_table()
        self.new_table = self.__get_end_row_table()
        self.new_table_head = self.new_table[:self.header]
        self.new_table_body = self.new_table[self.header:]

    def to_dict(self):
        return excel_format(self.new_table, self.header).to_dict

    def __get_start_row_table(self):
        for i in range(len(self.sheet_values)):
            if self.sheet_values[i][0] == self.table_name:
                start_row = i+self.null_row+1
                return self.sheet_values[start_row:]

    def __get_end_row_table(self):
        for i in range(len(self.new_table)):
            if [value for value in self.new_table[i] if value != None] == []:
                end_row = i
                return self.new_table[:end_row]
        return self.new_table


class unmerged_cell_fill:
    def __init__(self, sheet) -> None:
        self.sheet = sheet
        self.result = []
        self.__get_merged_cell()
        self.__fill()

    def __get_merged_cell(self):
        for merged_cell in self.sheet.merged_cells:
            min_row = merged_cell.min_row
            max_row = merged_cell.max_row
            min_col = merged_cell.min_col
            max_col = merged_cell.max_col
            # 读取合并单元格的值（以左上角的单元格为准）
            cell_value = self.sheet.cell(row=min_row, column=min_col).value
            self.result.append(
                (min_row, max_row, min_col, max_col, cell_value))

    def __fill(self):
        for min_row, max_row, min_col, max_col, cell_value in self.result:
            # print(min_row,max_row,min_col,max_col,cell_value)
            self.sheet.unmerge_cells(
                start_row=min_row, end_row=max_row, start_column=min_col,  end_column=max_col)
            for row in range(min_row, max_row+1):
                for column in range(min_col, max_col+1):
                    self.sheet.cell(row=row, column=column,).value = cell_value


class excel_format():
    def __init__(self, excel_value_list, header: int = 1,startrow:int=0) -> None:
        self.header = header
        self.table_head = excel_value_list[:header]
        self.table_body = excel_value_list[header:]
        self.table_len_clo = self.__get_table_clo()
        self.dict_key = self.__get_dict_key()
        self.to_dict = self.__to_dict()

    def __to_dict(self):
        
        result = []
        for table_unit in self.table_body:
            unit = {}
            for index , key in enumerate(self.dict_key):
                unit.update({key: table_unit[index]})
                
            # for i in range(self.table_len_clo):
            #     if table_unit[i]:
            #         unit.update({self.dict_key[i]: table_unit[i]})
            #     else:
            #         unit.update({self.dict_key[i]: ''})
            # print(unit)
            result.append(unit)
        return result

    def __get_dict_key(self):
        dict_key = []
        for i in range(self.table_len_clo):
            key = ''
            j = 0
            while self.header > j:
                b = self.table_head[j][i]
                if b:
                    if key:
                        key += f"//{b}"
                    else:
                        key += f"{b}"
                j += 1
            k = 1
            while True:
                if key not in dict_key:
                    dict_key.append(key)
                    break
                else:
                    if k == 1:
                        key = f'{key}_{k}'
                    else:
                        key = re.sub(r'_\d+$', f'_{k}', key)
                k += 1
        return dict_key

    def __get_table_clo(self):
        clo0 = [value for value in self.table_head[0] if value != None]
        return len(clo0)

def read_excel(sheet):
    return [row  for row in sheet.values ]


class input_table:
    def __init__(self, config) -> None:
        self.output_table_name = config['output_table']
        self.args=config['args']
        type = config['type']
        self.output_table:list
        
        if  type == 'excel':
            self.__excel()
        elif type == 'csv':
            pass
        elif type == 'sqllie':
            pass
        elif type == 'mysql':
            pass
        
        with shelve.open(database_path,writeback=True) as db:
            db['tables_dict'][self.output_table_name]=self.output_table

    def __excel(self):
        file=self.args['file']
        sheet=self.args['sheet']
        tablename=self.args.get('tablename',False)
        header=self.args['header']
        startrow=self.args['startrow']
        
        wb=load_workbook(file)
        ws = wb[sheet]
        unmerged_cell_fill(ws)
        if  tablename: ## 有tablename 读取sheet里面的表格
            self.output_table=find_sheet_table(read_excel(ws),table_name=tablename,header=header,null_row=startrow,).to_dict()
        else: ## 读取整个sheet
            # print(read_excel(ws))
              self.output_table= excel_format(read_excel(ws),header=header,startrow=0).to_dict

if __name__ == '__main__':
    path=r'D:\python\001MySortWare\网络运维工具\表格测试.xlsx'
    sheet='vty'
    wb=load_workbook(path)
    ws = wb[sheet]
    print(read_excel(ws))
